﻿/*
 * 创建人：盛金龙
 * 时间：2011/12/27 周二
 * 用途：曾删改用户
 */
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using EnvironmentManagerSystem.MODE;
using MySql.Data.MySqlClient;

namespace EnvironmentManagerSystem.BLL
{
    public class User
    {
        /// <summary>
        /// 获取用户
        /// </summary>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public static DataTable GetUser(string UserName)
        {
            try
            {
                string sql = "select * from tbs001_user where username like '%" + UserName + "%'";
                return DbHelperMySQL.Query(sql).Tables[0];
            }
            catch
            {
                return null;
            }
        }
        /// <summary>
        /// 获取用户列表
        /// </summary>
        public static DataTable GetUser(bool isAdmin, string UserName, string UserType, string RoleType)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" select ");
                br.Append(" id, ");
                br.Append(" UserName, ");
                br.Append(" LoginName, ");
                br.Append(" Password, ");
                if (isAdmin) br.Append(" Password as PasswordAdmin, ");
                else br.Append(" '******'as PasswordAdmin, ");
                br.Append(" Phone, ");
                br.Append(" RoleId, ");
                br.Append(" UserKind, ");
                br.Append(" InUse ");
                br.Append(" from tbs001_user ");
                br.Append(" where 1=1 ");
                if (UserName != string.Empty)
                    br.AppendFormat(" and UserName like '%{0}%'", UserName);
                if (UserType != string.Empty)
                    br.AppendFormat(" and UserKind='{0}'", UserType);
                if (RoleType != string.Empty)
                    br.AppendFormat(" and RoleId like '%{0}%'", RoleType);
                br.Append(" order by UserName desc ");
                return DbHelperMySQL.Query(br.ToString()).Tables[0];
            }
            catch { return null; }
        }

        /// <summary>
        /// 获取用户角色
        /// </summary>
        /// <returns></returns>
        public static DataTable GetRole()
        {
            try
            {
                return DbHelperMySQL.Query(" select id,RoleName as Name from tbs001_role WHERE InUse=1 ").Tables[0];
            }
            catch { return null; }
        }

        /// <summary>
        /// 删除用户数据
        /// </summary>
        /// <param name="p"></param>
        public static void DeleteUser(string id)
        {
            try
            {
                DbHelperMySQL.ExecuteSql(string.Format(" delete from tbs001_user where id='{0}'", id));
            }
            catch { }
        }

        /// <summary>
        /// 修改用户
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static Boolean UpdateUser(UserData use)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                //br.AppendFormat(" if( not exists(select UserName from tbs001_user where UserName = '{0}' and id != '{1}' ) ) ",str[0],str[7]);
                DataTable dt = DbHelperMySQL.Query(string.Format(" select UserName,LoginName from tbs001_user where (UserName = '{0}' or LoginName='{1}') and id != {2} ", use.UserName, use.LoginName, use.ID)).Tables[0];
                if (dt != null && dt.Rows.Count == 0)
                {
                    br.Append(" update tbs001_user set");
                    br.AppendFormat("  UserName = '{0}' ", use.UserName);
                    br.AppendFormat(" ,LoginName = '{0}' ", use.LoginName);
                    br.AppendFormat(" ,Password = '{0}' ", use.Password);
                    br.AppendFormat(" ,Phone = '{0}' ", use.Phone);
                    br.AppendFormat(" ,RoleId = '{0}' ", use.RoleId);
                    br.AppendFormat(" ,UserKind = '{0}' ", use.UserKind);
                    br.AppendFormat(" ,InUse = {0} ", use.InUse);
                    br.AppendFormat(" where id = {0} ", use.ID);
                    return DbHelperMySQL.ExecuteSql(br.ToString()) > 0 ? true : false;
                }
                return false;
            }
            catch { return false; }
        }
     

        /// <summary>
        /// 判断用户是否存在
        /// </summary>
        /// <param name="use"></param>
        /// <returns></returns>
        public static bool WhetherExistence(UserData use)
        {
            DataTable dt = DbHelperMySQL.Query(string.Format(" select LoginName,UserName from tbs001_user where LoginName = '{0}' or UserName = '{1}'", use.LoginName, use.UserName)).Tables[0];
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }

        #region 判断用户是否存在
        public static bool judgeUser(UserData use)
        {
            DataTable dt = DbHelperMySQL.Query(string.Format("select LoginName,UserName from tbs001_user where 1=1 and InUse=2 and (LoginName = '{0}' or UserName = '{1}')", use.LoginName, use.UserName)).Tables[0];
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }
        #endregion

        /// <summary>
        /// 判断用户是否存在
        /// </summary>
        /// <param name="use"></param>
        /// <returns></returns>
        public static bool WhetherExistence(UserData use, int EvaluationunitID)
        {
            DataTable dt = DbHelperMySQL.Query(string.Format(" select LoginName from tbs001_user where LoginName = '{0}' and evaluationunitID != {1} ", use.LoginName, EvaluationunitID)).Tables[0];
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 判断用户是否存在
        /// </summary>
        /// <param name="use"></param>
        /// <returns></returns>
        public static bool WhetherExistence2(UserData use)
        {
            DataTable dt = DbHelperMySQL.Query(string.Format(" select LoginName from tbs001_user where LoginName = '{0}'", use.LoginName)).Tables[0];
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 添加用户
        /// </summary>
        public static bool AddUser(UserData use)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" insert into tbs001_user (UserName,LoginName,Password,Phone,RoleId,UserKind,InUse,userid) values ( ");
                br.AppendFormat("'{0}',", use.UserName);
                br.AppendFormat("'{0}',", use.LoginName);
                br.AppendFormat("'{0}',", use.Password);
                br.AppendFormat("'{0}',", use.Phone);
                br.AppendFormat(" '{0}' ,", use.RoleId);
                br.AppendFormat("'{0}',", use.UserKind);
                br.AppendFormat(" {0},", use.InUse);
                br.AppendFormat(" {0} )", 0);
                return DbHelperMySQL.ExecuteSql(br.ToString()) > 0 ? true : false;
            }
            catch { return false; }
        }

        /// <summary>
        /// 进行用户验证
        /// </summary>
        public static DataTable login(string uid, string pwd)
        {
            try
            {
                string sql = " select ID,UserName,LoginName,PassWord,roleid,userid,evaluationunitID from tbs001_user where  LoginName = '{0}' and PassWord = '{1}'";
                return DbHelperMySQL.Query(string.Format(sql, uid, pwd)).Tables[0];
            }
            catch
            {
                return null;
            }
        }
        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="Password"></param>
        /// <returns></returns>
        public static bool UpdatePassword(string Password, string LoginName)
        {
            try
            {

                string strSql = string.Format("UPDATE tbs001_user SET Password='{0}' WHERE LoginName ='{1}'", Password, LoginName);
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            { return false; }
        }
        /// <summary>
        /// 获取用户权限
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public static string GetRoleID(int ID)
        {
            string sql = string.Format("select roleid from tbs001_user where ID={0}", ID);
            string RoleID = string.Empty;
            RoleID = DbHelperMySQL.Query(sql).Tables[0].Rows[0]["roleid"].ToString();
            return RoleID;
        }

        /// <summary>
        /// 修改用户角色
        /// </summary>
        /// <param name="RoleIDs"></param>
        /// <param name="ID"></param>
        /// <returns></returns>
        public static bool UpdateRole(string RoleIDs, int ID)
        {
            string sql = string.Format("update tbs001_user set roleid='{0}' where ID={1}", RoleIDs, ID);
            return DbHelperMySQL.ExecuteSql(sql) > 0 ? true : false;
        }

        /// <summary>
        /// 获取用户类型
        /// </summary>
        /// <returns></returns>
        public static UserDataCollection GetUserType()
        {
            string strSql = "select UserKind from tbs001_user WHERE UserKind IS NOT NULL AND UserKind !='' group by UserKind";
            UserDataCollection list = new UserDataCollection();
            using (MySqlDataReader dr = DbHelperMySQL.ExecuteReader(strSql))
            {
                while (dr.Read())
                {
                    UserData model = new UserData();
                    model.UserKind = dr["UserKind"].ToString();
                    list.Add(model);
                }
            }
            return list;
        }
    }
}
